package com.tbynet.jwp.repository.impl;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.tbynet.jwp.framework.aop.Aop;
import com.tbynet.jwp.framework.core.JwpRepository;
import com.tbynet.jwp.framework.exception.RepositoryException;
import com.tbynet.jwp.repository.spi._TermQueryRepository;
import com.tbynet.jwp.repository.dto.query.TermQuery;
import com.tbynet.jwp.repository.dto.result.TermResult;

import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 分类项查询仓储实现类
 * 负责分类项的查询、搜索、统计等读操作
 *
 * 注意：Repository层无事务，只负责数据查询
 * 异常处理原则：
 * - 数据访问异常抛出 RepositoryException
 *
 * @author 佰亿互联
 * @version 1.0
 * @since 2025-11-04
 */
@Aop(_TermQueryRepository.class)
public class _TermQueryRepositoryImpl extends JwpRepository implements _TermQueryRepository {

    /**
     * 根据ID获取分类项详情
     *
     * @param termId 分类项ID
     * @param taxonomy 分类法类型
     * @return 分类项详情
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public TermResult getTermById(BigInteger termId, String taxonomy) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("查询分类项详情", () -> {
            // 构建SQL查询
            String sql = "SELECT t.term_id, t.name, t.slug, t.term_group, " +
                    "tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.term_id = ? AND tt.taxonomy = ?";

            Record record = Db.findFirst(sql, termId, taxonomy);
            if (record == null) {
                throw new RepositoryException(String.format("分类项-ID: %s, Taxonomy: %s", termId, taxonomy));
            }

            return convertToTermResult(record);
        });
    }

    /**
     * 根据别名获取分类项
     *
     * @param slug 分类项别名
     * @param taxonomy 分类法类型
     * @return 分类项详情
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public TermResult getTermBySlug(String slug, String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(slug)) {
            throw new RepositoryException("分类项别名不能为空").withContext("slug", slug);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("根据别名获取分类项", () -> {
            // 构建SQL查询
            String sql = "SELECT t.term_id, t.name, t.slug, t.term_group, " +
                    "tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.slug = ? AND tt.taxonomy = ?";

            Record record = Db.findFirst(sql, slug, taxonomy);
            if (record == null) {
                log.debug("分类项不存在，slug: %s, taxonomy: %s", slug, taxonomy);
                return null;
            }

            return convertToTermResult(record);
        });
    }

    /**
     * 根据名称获取分类项
     *
     * @param name 分类项名称
     * @param taxonomy 分类法类型
     * @return 分类项详情
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public TermResult getTermByName(String name, String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(name)) {
            throw new RepositoryException("分类项名称不能为空").withContext("name", name);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("根据名称获取分类项", () -> {
            // 构建SQL查询
            String sql = "SELECT t.term_id, t.name, t.slug, t.term_group, " +
                    "tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.name = ? AND tt.taxonomy = ?";

            Record record = Db.findFirst(sql, name, taxonomy);
            if (record == null) {
                log.debug("分类项不存在，name: %s, taxonomy: %s", name, taxonomy);
                return null;
            }

            return convertToTermResult(record);
        });
    }

    /**
     * 查询分类项列表
     *
     * @param query 查询条件
     * @return 分类项列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> listTerms(TermQuery query) {
        // 参数验证
        if (query == null) {
            throw new RepositoryException("查询条件不能为空").withContext("query", query);
        }

        return executeDataOperation("查询分类项列表", () -> {
            // 构建查询SQL
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("SELECT t.term_id, t.name, t.slug, t.term_group, ");
            sqlBuilder.append("tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count ");
            sqlBuilder.append("FROM wp_terms t ");
            sqlBuilder.append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            sqlBuilder.append("WHERE 1=1 ");

            List<Object> params = new ArrayList<>();

            // 添加查询条件
            if (query.getTaxonomies() != null && !query.getTaxonomies().isEmpty()) {
                sqlBuilder.append("AND tt.taxonomy IN (");
                String placeholders = query.getTaxonomies().stream()
                        .map(tax -> "?")
                        .collect(Collectors.joining(","));
                sqlBuilder.append(placeholders).append(") ");
                params.addAll(query.getTaxonomies());
            }

            if (query.getTermIds() != null && !query.getTermIds().isEmpty()) {
                sqlBuilder.append("AND t.term_id IN (");
                String placeholders = query.getTermIds().stream()
                        .map(id -> "?")
                        .collect(Collectors.joining(","));
                sqlBuilder.append(placeholders).append(") ");
                params.addAll(query.getTermIds());
            }

            if (query.getParentId() != null) {
                sqlBuilder.append("AND tt.parent = ? ");
                params.add(query.getParentId());
            }

            if (Boolean.TRUE.equals(query.getHideEmpty())) {
                sqlBuilder.append("AND tt.count > 0 ");
            }

            if (StrKit.notBlank(query.getSearch())) {
                sqlBuilder.append("AND (t.name LIKE ? OR t.slug LIKE ?) ");
                params.add("%" + query.getSearch() + "%");
                params.add("%" + query.getSearch() + "%");
            }

            // 添加排序
            if (StrKit.notBlank(query.getOrderBy())) {
                sqlBuilder.append("ORDER BY ");
                switch (query.getOrderBy()) {
                    case "name":
                        sqlBuilder.append("t.name ");
                        break;
                    case "slug":
                        sqlBuilder.append("t.slug ");
                        break;
                    case "count":
                        sqlBuilder.append("tt.count ");
                        break;
                    case "term_id":
                    default:
                        sqlBuilder.append("t.term_id ");
                        break;
                }

                if (StrKit.notBlank(query.getOrder()) && "DESC".equalsIgnoreCase(query.getOrder())) {
                    sqlBuilder.append("DESC ");
                } else {
                    sqlBuilder.append("ASC ");
                }
            }

            // 添加分页限制
            if (query.getPageSize() != null && query.getPageSize() > 0) {
                sqlBuilder.append("LIMIT ? ");
                params.add(query.getPageSize());

                if (query.getPageNumber() != null && query.getPageNumber() > 1) {
                    sqlBuilder.append("OFFSET ? ");
                    params.add((query.getPageNumber() - 1) * query.getPageSize());
                }
            }

            // 执行查询
            List<Record> records = Db.find(sqlBuilder.toString(), params.toArray());
            return records.stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 分页查询分类项列表
     *
     * @param query 查询条件
     * @return 分页结果
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Page<TermResult> paginateTerms(TermQuery query) {
        // 参数验证
        if (query == null) {
            throw new RepositoryException("查询条件不能为空").withContext("query", query);
        }

        return executeDataOperation("分页查询分类项列表", () -> {
            // 计算页码和页大小
            int pageNumber = query.getPageNumber() != null ? query.getPageNumber() : 1;
            int pageSize = query.getPageSize() != null ? query.getPageSize() : 20;

            // 构建查询SQL
            StringBuilder selectSql = new StringBuilder();
            selectSql.append("SELECT t.term_id, t.name, t.slug, t.term_group, ");
            selectSql.append("tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count ");

            StringBuilder fromSql = new StringBuilder();
            fromSql.append("FROM wp_terms t ");
            fromSql.append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            fromSql.append("WHERE 1=1 ");

            List<Object> params = new ArrayList<>();

            // 添加查询条件
            if (query.getTaxonomies() != null && !query.getTaxonomies().isEmpty()) {
                fromSql.append("AND tt.taxonomy IN (");
                String placeholders = query.getTaxonomies().stream()
                        .map(tax -> "?")
                        .collect(Collectors.joining(","));
                fromSql.append(placeholders).append(") ");
                params.addAll(query.getTaxonomies());
            }

            if (query.getTermIds() != null && !query.getTermIds().isEmpty()) {
                fromSql.append("AND t.term_id IN (");
                String placeholders = query.getTermIds().stream()
                        .map(id -> "?")
                        .collect(Collectors.joining(","));
                fromSql.append(placeholders).append(") ");
                params.addAll(query.getTermIds());
            }

            if (query.getParentId() != null) {
                fromSql.append("AND tt.parent = ? ");
                params.add(query.getParentId());
            }

            if (Boolean.TRUE.equals(query.getHideEmpty())) {
                fromSql.append("AND tt.count > 0 ");
            }

            if (StrKit.notBlank(query.getSearch())) {
                fromSql.append("AND (t.name LIKE ? OR t.slug LIKE ?) ");
                params.add("%" + query.getSearch() + "%");
                params.add("%" + query.getSearch() + "%");
            }

            // 添加排序
            if (StrKit.notBlank(query.getOrderBy())) {
                fromSql.append("ORDER BY ");
                switch (query.getOrderBy()) {
                    case "name":
                        fromSql.append("t.name ");
                        break;
                    case "slug":
                        fromSql.append("t.slug ");
                        break;
                    case "count":
                        fromSql.append("tt.count ");
                        break;
                    case "term_id":
                    default:
                        fromSql.append("t.term_id ");
                        break;
                }

                if (StrKit.notBlank(query.getOrder()) && "DESC".equalsIgnoreCase(query.getOrder())) {
                    fromSql.append("DESC ");
                } else {
                    fromSql.append("ASC ");
                }
            }

            // 执行分页查询
            Page<Record> recordPage = Db.paginate(pageNumber, pageSize,
                    selectSql.toString(), fromSql.toString(), params.toArray());

            // 转换为TermResult列表
            List<TermResult> termResults = recordPage.getList().stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());

            return new Page<>(termResults, recordPage.getPageNumber(),
                    recordPage.getPageSize(), recordPage.getTotalPage(),
                    recordPage.getTotalRow());
        });
    }

    /**
     * 获取分类项层级结构
     *
     * @param taxonomy 分类法类型
     * @param parentId 父级ID（可选）
     * @return 层级结构列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getTermHierarchy(String taxonomy, BigInteger parentId) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取分类项层级结构", () -> {
            // 构建查询SQL
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("SELECT t.term_id, t.name, t.slug, t.term_group, ");
            sqlBuilder.append("tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count ");
            sqlBuilder.append("FROM wp_terms t ");
            sqlBuilder.append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            sqlBuilder.append("WHERE tt.taxonomy = ? ");

            List<Object> params = new ArrayList<>();
            params.add(taxonomy);

            if (parentId != null) {
                sqlBuilder.append("AND tt.parent = ? ");
                params.add(parentId);
            } else {
                sqlBuilder.append("AND tt.parent = 0 ");
            }

            sqlBuilder.append("ORDER BY t.name ASC");

            // 执行查询
            List<Record> records = Db.find(sqlBuilder.toString(), params.toArray());
            return records.stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 根据文章ID获取关联的分类项
     *
     * @param postId 文章ID
     * @param taxonomy 分类法类型
     * @return 关联的分类项列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getTermsByPostId(BigInteger postId, String taxonomy) {
        // 参数验证
        if (postId == null) {
            throw new RepositoryException("文章ID不能为空").withContext("postId", postId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("根据文章获取分类项", () -> {
            // 构建查询SQL
            String sql = "SELECT t.term_id, t.name, t.slug, t.term_group, " +
                    "tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tr.object_id = ? AND tt.taxonomy = ? " +
                    "ORDER BY t.name ASC";

            List<Record> records = Db.find(sql, postId, taxonomy);
            return records.stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 获取分类项元数据
     *
     * @param termId 分类项ID
     * @param metaKey 元数据键（可选，为空时返回所有元数据）
     * @return 元数据值
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Object getTermMeta(BigInteger termId, String metaKey) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }

        return executeDataOperation("获取分类项元数据", () -> {
            if (StrKit.isBlank(metaKey)) {
                // 获取所有元数据
                List<Record> metaRecords = Db.find(
                        "SELECT meta_key, meta_value FROM wp_termmeta WHERE term_id = ?", termId);

                Map<String, Object> metaMap = new HashMap<>();
                for (Record record : metaRecords) {
                    metaMap.put(record.getStr("meta_key"), record.get("meta_value"));
                }
                return metaMap;
            } else {
                // 获取指定元数据
                Record metaRecord = Db.findFirst(
                        "SELECT meta_value FROM wp_termmeta WHERE term_id = ? AND meta_key = ?",
                        termId, metaKey);

                return metaRecord != null ? metaRecord.get("meta_value") : null;
            }
        });
    }

    /**
     * 检查分类项是否存在
     *
     * @param term 分类项标识（名称、别名或ID）
     * @param taxonomy 分类法类型
     * @return 分类项ID（存在时返回）
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public BigInteger termExists(Object term, String taxonomy) {
        // 参数验证
        if (term == null) {
            throw new RepositoryException("分类项标识不能为空").withContext("term", term);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("检查分类项是否存在", () -> {
            String sql;
            Object param;

            if (term instanceof BigInteger) {
                // 按ID检查
                sql = "SELECT t.term_id FROM wp_terms t " +
                        "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                        "WHERE t.term_id = ? AND tt.taxonomy = ?";
                param = term;
            } else if (term instanceof String) {
                String termStr = (String) term;
                // 按名称或别名检查
                sql = "SELECT t.term_id FROM wp_terms t " +
                        "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                        "WHERE (t.name = ? OR t.slug = ?) AND tt.taxonomy = ?";
                param = termStr;
            } else {
                throw new RepositoryException("不支持的分类项标识类型: " + term.getClass().getSimpleName()).withContext("term", term);
            }

            Record record;
            if (term instanceof BigInteger) {
                record = Db.findFirst(sql, param, taxonomy);
            } else {
                record = Db.findFirst(sql, param, param, taxonomy);
            }

            return record != null ? record.getBigInteger("term_id") : null;
        });
    }

    /**
     * 检查分类项别名是否存在
     *
     * @param slug 分类项别名
     * @param taxonomy 分类法类型
     * @return 是否存在
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public boolean isSlugExists(String slug, String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(slug)) {
            throw new RepositoryException("分类项别名不能为空").withContext("slug", slug);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("检查分类项别名是否存在", () -> {
            String sql = "SELECT COUNT(*) as count FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE t.slug = ? AND tt.taxonomy = ?";

            Record record = Db.findFirst(sql, slug, taxonomy);
            return record != null && record.getLong("count") > 0;
        });
    }

    /**
     * 统计分类项数量
     *
     * @param taxonomy 分类法类型
     * @param query 查询条件（可选）
     * @return 分类项数量
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Long countTerms(String taxonomy, TermQuery query) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("统计分类项数量", () -> {
            // 构建查询SQL
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("SELECT COUNT(*) as total FROM wp_terms t ");
            sqlBuilder.append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            sqlBuilder.append("WHERE tt.taxonomy = ? ");

            List<Object> params = new ArrayList<>();
            params.add(taxonomy);

            // 添加查询条件
            if (query != null) {
                if (query.getParentId() != null) {
                    sqlBuilder.append("AND tt.parent = ? ");
                    params.add(query.getParentId());
                }

                if (Boolean.TRUE.equals(query.getHideEmpty())) {
                    sqlBuilder.append("AND tt.count > 0 ");
                }

                if (StrKit.notBlank(query.getSearch())) {
                    sqlBuilder.append("AND (t.name LIKE ? OR t.slug LIKE ?) ");
                    params.add("%" + query.getSearch() + "%");
                    params.add("%" + query.getSearch() + "%");
                }
            }

            Record record = Db.findFirst(sqlBuilder.toString(), params.toArray());
            return record != null ? record.getLong("total") : 0L;
        });
    }

    /**
     * 按分类法统计分类项数量
     *
     * @param taxonomies 分类法类型列表
     * @return 各分类法的分类项数量映射
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Map<String, Long> countTermsByTaxonomies(List<String> taxonomies) {
        // 参数验证
        if (taxonomies == null || taxonomies.isEmpty()) {
            throw new RepositoryException("分类法类型列表不能为空").withContext("taxonomies", taxonomies);
        }

        return executeDataOperation("按分类法统计分类项数量", () -> {
            // 构建查询SQL
            String placeholders = taxonomies.stream()
                    .map(tax -> "?")
                    .collect(Collectors.joining(","));

            String sql = "SELECT tt.taxonomy, COUNT(*) as count " +
                    "FROM wp_term_taxonomy tt " +
                    "WHERE tt.taxonomy IN (" + placeholders + ") " +
                    "GROUP BY tt.taxonomy";

            List<Record> records = Db.find(sql, taxonomies.toArray());

            Map<String, Long> result = new HashMap<>();
            for (Record record : records) {
                result.put(record.getStr("taxonomy"), record.getLong("count"));
            }

            return result;
        });
    }

    /**
     * 获取热门分类项
     *
     * @param taxonomy 分类法类型
     * @param limit 限制数量
     * @return 热门分类项列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getPopularTerms(String taxonomy, Integer limit) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取热门分类项", () -> {
            int actualLimit = limit != null ? limit : 10;

            // 构建查询SQL
            String sql = "SELECT t.term_id, t.name, t.slug, t.term_group, " +
                    "tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy = ? AND tt.count > 0 " +
                    "ORDER BY tt.count DESC " +
                    "LIMIT ?";

            List<Record> records = Db.find(sql, taxonomy, actualLimit);
            return records.stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 搜索分类项
     *
     * @param keyword 搜索关键词
     * @param taxonomy 分类法类型（可选）
     * @param limit 限制数量
     * @return 搜索结果列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> searchTerms(String keyword, String taxonomy, Integer limit) {
        // 参数验证
        if (StrKit.isBlank(keyword)) {
            throw new RepositoryException("搜索关键词不能为空").withContext("keyword", keyword);
        }

        return executeDataOperation("搜索分类项", () -> {
            int actualLimit = limit != null ? limit : 20;

            // 构建查询SQL
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("SELECT t.term_id, t.name, t.slug, t.term_group, ");
            sqlBuilder.append("tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count ");
            sqlBuilder.append("FROM wp_terms t ");
            sqlBuilder.append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            sqlBuilder.append("WHERE (t.name LIKE ? OR t.slug LIKE ?) ");

            List<Object> params = new ArrayList<>();
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");

            if (StrKit.notBlank(taxonomy)) {
                sqlBuilder.append("AND tt.taxonomy = ? ");
                params.add(taxonomy);
            }

            sqlBuilder.append("ORDER BY t.name ASC ");
            sqlBuilder.append("LIMIT ?");
            params.add(actualLimit);

            List<Record> records = Db.find(sqlBuilder.toString(), params.toArray());
            return records.stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 获取分类法信息
     *
     * @param taxonomy 分类法类型
     * @return 分类法信息映射
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Map<String, Object> getTaxonomyInfo(String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy",taxonomy);
        }

        return executeDataOperation("获取分类法信息", () -> {
            Map<String, Object> taxonomyInfo = new HashMap<>();

            // 基础信息
            taxonomyInfo.put("taxonomy", taxonomy);
            taxonomyInfo.put("label", getTaxonomyLabel(taxonomy));
            taxonomyInfo.put("description", getTaxonomyDescription(taxonomy));
            taxonomyInfo.put("hierarchical", isTaxonomyHierarchical(taxonomy));
            taxonomyInfo.put("public", true); // 默认公开

            // 统计信息
            Long termCount = countTerms(taxonomy, null);
            taxonomyInfo.put("termCount", termCount);

            // 默认分类项
            TermResult defaultTerm = getDefaultTerm(taxonomy);
            if (defaultTerm != null) {
                Map<String, Object> defaultTermInfo = new HashMap<>();
                defaultTermInfo.put("termId", defaultTerm.getTermId());
                defaultTermInfo.put("name", defaultTerm.getName());
                defaultTermInfo.put("slug", defaultTerm.getSlug());
                taxonomyInfo.put("defaultTerm", defaultTermInfo);
            }

            // 设置信息
            Map<String, Object> settings = new HashMap<>();
            settings.put("showInMenu", true);
            settings.put("showInAdmin", true);
            settings.put("allowMultiple", !taxonomy.equals("category")); // 分类通常单选，标签多选
            taxonomyInfo.put("settings", settings);

            return taxonomyInfo;
        });
    }

    /**
     * 获取完整树形结构
     *
     * @param taxonomy 分类法类型
     * @return 树形结构根节点列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getTermTree(String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取完整树形结构", () -> {
            // 获取所有分类项
            TermQuery query = new TermQuery();
            query.setTaxonomies(Collections.singletonList(taxonomy));
            List<TermResult> allTerms = listTerms(query);

            // 构建树形结构
            Map<BigInteger, TermResult> termMap = new HashMap<>();
            List<TermResult> rootTerms = new ArrayList<>();

            // 建立映射
            for (TermResult term : allTerms) {
                // 添加空值检查
                if (term.getTermId() == null) {
                    log.warn("发现termId为null的分类项，跳过: name=%s", term.getName());
                    continue;
                }
                termMap.put(term.getTermId(), term);
            }

            // 构建父子关系
            for (TermResult term : allTerms) {
                // 添加空值检查
                if (term.getTermId() == null) {
                    continue;
                }

                BigInteger parentId = term.getParentId();
                if (parentId == null || parentId.equals(BigInteger.ZERO) || !termMap.containsKey(parentId)) {
                    rootTerms.add(term);
                } else {
                    TermResult parent = termMap.get(parentId);
                    if (parent.getChildren() == null) {
                        parent.setChildren(new ArrayList<>());
                    }
                    parent.getChildren().add(term);
                }
            }

            return rootTerms;
        });
    }

    /**
     * 检查祖先关系
     *
     * @param termId 分类项ID
     * @param ancestorId 祖先分类项ID
     * @param taxonomy 分类法类型
     * @return 是否存在祖先关系
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public boolean isTermAncestor(BigInteger termId, BigInteger ancestorId, String taxonomy) {
        // 参数验证
        if (termId == null || ancestorId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("检查祖先关系", () -> {
            if (termId.equals(ancestorId)) {
                return false; // 自己不能是自己的祖先
            }

            // 递归检查父级链
            BigInteger currentParentId = termId;
            int maxDepth = 20; // 防止无限循环

            while (currentParentId != null && !currentParentId.equals(BigInteger.ZERO) && maxDepth > 0) {
                TermResult currentTerm = getTermById(currentParentId, taxonomy);
                if (currentTerm == null) {
                    break;
                }

                if (currentTerm.getParentId() != null && currentTerm.getParentId().equals(ancestorId)) {
                    return true;
                }

                currentParentId = currentTerm.getParentId();
                maxDepth--;
            }

            return false;
        });
    }

    /**
     * 获取默认分类项
     *
     * @param taxonomy 分类法类型
     * @return 默认分类项
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public TermResult getDefaultTerm(String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取默认分类项", () -> {
            // 从系统选项获取默认分类项ID
            String optionName = "default_" + taxonomy;
            Record option = Db.findFirst("SELECT option_value FROM wp_options WHERE option_name = ?", optionName);

            if (option != null) {
                String termIdStr = option.getStr("option_value");
                try {
                    BigInteger termId = new BigInteger(termIdStr);
                    return getTermById(termId, taxonomy);
                } catch (NumberFormatException e) {
                    log.error("默认分类项ID格式错误: %s", termIdStr);
                }
            }

            // 如果没有设置默认分类项，返回第一个分类项
            List<TermResult> terms = listTerms(new TermQuery());
            return terms.isEmpty() ? null : terms.get(0);
        });
    }

    /**
     * 获取分类项路径
     *
     * @param termId 分类项ID
     * @param taxonomy 分类法类型
     * @return 从根到当前项的路径
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getTermPath(BigInteger termId, String taxonomy) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取分类项路径", () -> {
            // 使用LinkedList提高头部插入性能
            LinkedList<TermResult> path = new LinkedList<>();
            Set<BigInteger> visited = new HashSet<>(); // 防循环
            BigInteger currentId = termId;
            int maxDepth = 100; // 增加到更安全的深度限制

            while (currentId != null &&
                    !currentId.equals(BigInteger.ZERO) &&
                    maxDepth > 0 &&
                    !visited.contains(currentId)) {

                visited.add(currentId);
                TermResult currentTerm = getTermById(currentId, taxonomy);

                if (currentTerm == null) {
                    log.warn("获取分类项路径时发现不存在的分类项: termId=%s", currentId);
                    break;
                }

                // 添加到链表开头（性能更好）
                path.addFirst(currentTerm);

                // 检查是否到达根节点
                BigInteger parentId = currentTerm.getParentId();
                if (parentId == null || parentId.equals(BigInteger.ZERO)) {
                    break;
                }

                // 修复：检测到循环时立即退出
                if (visited.contains(parentId)) {
                    log.error("检测到分类项父子关系循环: termId=%s, parentId=%s", currentId, parentId);
                    break;
                }

                currentId = parentId;
                maxDepth--;
            }

            // 深度超限警告
            if (maxDepth == 0) {
                log.error("获取分类项路径达到最大深度限制，可能存在循环: termId=%s, taxonomy=%s", termId, taxonomy);
            }

            return new ArrayList<>(path);
        });
    }

    /**
     * 获取同层级分类项
     *
     * @param termId 分类项ID
     * @param taxonomy 分类法类型
     * @return 同层级分类项列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getSiblingTerms(BigInteger termId, String taxonomy) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy",taxonomy);
        }

        return executeDataOperation("获取同层级分类项", () -> {
            // 获取当前分类项的父级ID
            TermResult currentTerm = getTermById(termId, taxonomy);
            if (currentTerm == null) {
                throw new RepositoryException("分类项不存在").withContext("termId", termId);
            }

            BigInteger parentId = currentTerm.getParentId();
            if (parentId == null) {
                parentId = BigInteger.ZERO;
            }

            // 查询同父级的分类项（排除自己）
            TermQuery query = new TermQuery();
            query.setTaxonomies(Collections.singletonList(taxonomy));
            query.setParentId(parentId);

            List<TermResult> siblings = listTerms(query);
            siblings.removeIf(term -> term.getTermId().equals(termId));

            return siblings;
        });
    }

    /**
     * 获取分类项深度
     *
     * @param termId 分类项ID
     * @param taxonomy 分类法类型
     * @return 分类项深度
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Integer getTermDepth(BigInteger termId, String taxonomy) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取分类项深度", () -> {
            List<TermResult> path = getTermPath(termId, taxonomy);
            return Math.max(0, path.size() - 1); // 深度 = 路径长度 - 1
        });
    }

    /**
     * 检查分类项是否为空
     *
     * @param termId 分类项ID
     * @param taxonomy 分类法类型
     * @return 是否为空
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public boolean isTermEmpty(BigInteger termId, String taxonomy) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("检查分类项是否为空", () -> {
            TermResult term = getTermById(termId, taxonomy);
            return term == null || term.getCount() == null || term.getCount() == 0;
        });
    }

    /**
     * 获取分类项使用统计
     *
     * @param taxonomy 分类法类型
     * @param days 统计天数
     * @return 使用统计信息
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Map<String, Object> getTermUsageStatistics(String taxonomy, Integer days) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取分类项使用统计", () -> {
            int actualDays = days != null ? days : 30;

            Map<String, Object> statistics = new HashMap<>();

            // 总体使用统计
            Long totalUsage = countTerms(taxonomy, null);
            statistics.put("totalTerms", totalUsage);
            statistics.put("analysisPeriod", actualDays + "天");

            // 近期使用趋势（简化实现）
            Map<String, Long> dailyUsage = new HashMap<>();
            Calendar calendar = Calendar.getInstance();

            for (int i = 0; i < actualDays; i++) {
                calendar.add(Calendar.DAY_OF_YEAR, -i);
                String dateKey = String.format("%tF", calendar);
                // 这里简化处理，实际应该查询每天的使用数据
                dailyUsage.put(dateKey, (long) (Math.random() * 10));
            }
            statistics.put("dailyUsage", dailyUsage);

            // 热门分类项
            List<TermResult> popularTerms = getPopularTerms(taxonomy, 5);
            statistics.put("popularTerms", popularTerms);

            // 分析洞察
            Map<String, Object> insights = new HashMap<>();
            insights.put("avgUsagePerTerm", totalUsage > 0 ? Math.round(totalUsage * 10.0 / totalUsage) : 0);
            insights.put("growthTrend", "stable");
            insights.put("recommendation", "保持当前分类结构");
            statistics.put("insights", insights);

            return statistics;
        });
    }

    /**
     * 获取最近更新的分类项
     *
     * @param taxonomy 分类法类型
     * @param limit 限制数量
     * @return 最近更新的分类项列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<TermResult> getRecentlyUpdatedTerms(String taxonomy, Integer limit) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取最近更新的分类项", () -> {
            int actualLimit = limit != null ? limit : 10;

            // 注意：这里假设表中有update_time字段，实际根据表结构调整
            String sql = "SELECT t.term_id, t.name, t.slug, t.term_group, " +
                    "tt.term_taxonomy_id, tt.taxonomy, tt.description, tt.parent, tt.count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "WHERE tt.taxonomy = ? " +
                    "ORDER BY tt.term_taxonomy_id DESC " + // 使用ID排序模拟时间排序
                    "LIMIT ?";

            List<Record> records = Db.find(sql, taxonomy, actualLimit);
            return records.stream()
                    .map(this::convertToTermResult)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 验证分类项别名唯一性
     *
     * @param slug 分类项别名
     * @param taxonomy 分类法类型
     * @param excludeTermId 排除的分类项ID
     * @return 是否唯一
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public boolean isSlugUnique(String slug, String taxonomy, BigInteger excludeTermId) {
        // 参数验证
        if (StrKit.isBlank(slug)) {
            throw new RepositoryException("分类项别名不能为空").withContext("slug", slug);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("验证分类项别名唯一性", () -> {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT COUNT(*) as count FROM wp_terms t ");
            sql.append("INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id ");
            sql.append("WHERE t.slug = ? AND tt.taxonomy = ? ");

            if (excludeTermId != null) {
                sql.append("AND t.term_id != ?");
            }

            Record record;
            if (excludeTermId != null) {
                record = Db.findFirst(sql.toString(), slug, taxonomy, excludeTermId);
            } else {
                record = Db.findFirst(sql.toString(), slug, taxonomy);
            }

            return record != null && record.getLong("count") == 0;
        });
    }

    /**
     * 获取分类项关联的文章ID列表
     *
     * @param termId 分类项ID
     * @param taxonomy 分类法类型
     * @return 关联的文章ID列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<BigInteger> getObjectsInTerm(BigInteger termId, String taxonomy) {
        // 参数验证
        if (termId == null) {
            throw new RepositoryException("分类项ID不能为空").withContext("termId", termId);
        }
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("获取分类项关联的文章ID列表", () -> {
            // 获取分类法ID
            Record termTaxonomy = Db.findFirst(
                    "SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = ? AND taxonomy = ?",
                    termId, taxonomy);
            if (termTaxonomy == null) {
                throw new RepositoryException(String.format("分类法-term_id=%s, taxonomy=%s", termId, taxonomy));
            }

            Long taxonomyId = termTaxonomy.getLong("term_taxonomy_id");

            // 查询关联的文章ID
            String sql = "SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id = ?";
            List<Record> records = Db.find(sql, taxonomyId);

            return records.stream()
                    .map(record -> record.get("object_id"))
                    .filter(Objects::nonNull)
                    .map(obj -> {
                        if (obj instanceof Long) {
                            return BigInteger.valueOf((Long) obj);
                        } else if (obj instanceof BigInteger) {
                            return (BigInteger) obj;
                        }
                        return null;
                    })
                    .filter(Objects::nonNull)
                    .collect(Collectors.toList());
        });
    }

    /**
     * 查找未使用的分类项
     *
     * @param taxonomy 分类法类型
     * @return 未使用的分类项ID列表
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public List<BigInteger> findUnusedTerms(String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("查找未使用的分类项", () -> {
            // 查询未使用的分类项
            // 使用LEFT JOIN查找没有关联文章的分类项
            String sql = "SELECT t.term_id " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tt.taxonomy = ? " +
                    "AND tr.object_id IS NULL " +
                    "AND tt.count = 0 " +
                    "ORDER BY t.term_id";

            List<Record> records = Db.find(sql, taxonomy);

            // 转换为BigInteger列表
            List<BigInteger> unusedTermIds = records.stream()
                    .map(record -> record.getBigInteger("term_id"))
                    .collect(Collectors.toList());

            log.debug("查找未使用分类项完成: taxonomy=%s, count=%s", taxonomy, unusedTermIds.size());

            return unusedTermIds;
        });
    }

    /**
     * 统计空分类项数量
     *
     * @param taxonomy 分类法类型
     * @return 空分类项数量
     * @throws RepositoryException 数据访问异常
     */
    @Override
    public Long countEmptyTerms(String taxonomy) {
        // 参数验证
        if (StrKit.isBlank(taxonomy)) {
            throw new RepositoryException("分类法类型不能为空").withContext("taxonomy", taxonomy);
        }

        return executeDataOperation("统计空分类项数量", () -> {
            // 统计空分类项数量
            // 使用COUNT和LEFT JOIN统计没有关联文章的分类项数量
            String sql = "SELECT COUNT(DISTINCT t.term_id) as empty_count " +
                    "FROM wp_terms t " +
                    "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                    "LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id " +
                    "WHERE tt.taxonomy = ? " +
                    "AND tr.object_id IS NULL " +
                    "AND tt.count = 0";

            Record result = Db.findFirst(sql, taxonomy);

            Long emptyCount = result != null ? result.getLong("empty_count") : 0L;

            log.debug("统计空分类项数量完成: taxonomy=%s, count=%s", taxonomy, emptyCount);

            return emptyCount;
        });
    }

    // ========== 私有工具方法 ==========

    /**
     * 获取分类法显示标签
     *
     * @param taxonomy 分类法类型
     * @return 显示标签
     */
    private String getTaxonomyLabel(String taxonomy) {
        switch (taxonomy) {
            case "category":
                return "分类";
            case "post_tag":
                return "标签";
            case "nav_menu":
                return "菜单";
            default:
                return taxonomy;
        }
    }

    /**
     * 获取分类法描述
     *
     * @param taxonomy 分类法类型
     * @return 分类法描述
     */
    private String getTaxonomyDescription(String taxonomy) {
        switch (taxonomy) {
            case "category":
                return "文章分类管理";
            case "post_tag":
                return "文章标签管理";
            case "nav_menu":
                return "导航菜单管理";
            default:
                return "自定义分类法";
        }
    }

    /**
     * 检查分类法是否支持层级结构
     *
     * @param taxonomy 分类法类型
     * @return 是否支持层级结构
     */
    private Boolean isTaxonomyHierarchical(String taxonomy) {
        return "category".equals(taxonomy) || "nav_menu".equals(taxonomy);
    }

    /**
     * 将Record转换为TermResult
     *
     * @param record 数据库记录
     * @return 转换后的TermResult
     */
    private TermResult convertToTermResult(Record record) {
        if (record == null) {
            return null;
        }

        TermResult result = new TermResult();
        result.setTermId(record.get("term_id"));
        result.setName(record.getStr("name"));
        result.setSlug(record.getStr("slug"));
        result.setTaxonomy(record.getStr("taxonomy"));
        result.setTaxonomyId(record.get("term_taxonomy_id"));
        result.setDescription(record.getStr("description"));
        result.setParentId(record.get("parent"));
        result.setCount(record.getInt("count"));
        result.setTermGroup(record.getInt("term_group"));

        // 设置时间字段（如果表中有这些字段）
        // result.setCreateTime(record.getDate("create_time"));
        // result.setUpdateTime(record.getDate("update_time"));

        return result;
    }
}